package Zoo.impl;

import Zoo.entity.AnimalEntity;

import Zoo.service.ZoomenService1;
import Zoo.util.TableDTO;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 * 1、查看动物饲养员的管理园区的动物的信息
 * 2、动物饲养员增删改查动物功能
 */

public class ZoomenService1Impl implements ZoomenService1 {

    //查询功能
    @Override
    public TableDTO retrieveZoomen1(AnimalEntity animalEntity, String zoomenName1) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT a.`id`,a.`name`,a.`kind`,a.`introduction`,`age`,`health`,`parkid` FROM `animals` AS a " +
                " LEFT JOIN `zooparks` AS zp " +
                " ON a.`parkid`=zp.`id`" +
                " LEFT JOIN  `zoomen` zm " +
                " ON zp.`adminid`=zm.`name` " +
                " WHERE zm.`name`= '" + zoomenName1 + "'");
        if (animalEntity.getSearchKey() != null && !"".equals(animalEntity.getSearchKey().trim())) {
            sql.append("and a.`name` like '%" + animalEntity.getSearchKey().trim() + "%'");
        }
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        //查出来的数据存在这里
        TableDTO returnDTO = new TableDTO();


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());
            rs = st.executeQuery();
            //存放连接数据库查出来的数据
            returnDTO.setData(fillData(rs));
            ;


            return returnDTO;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            com.JdbcUtils_DBCP.release(conn, st, rs);
        }
        return null;
    }

    //从数据库查出来的数据
    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            Vector<Object> oneRecord = new Vector<>();

            String animalId = rs.getString("id");
            String name = rs.getString("name");
            String kind = rs.getString("kind");
            String introduction = rs.getString("introduction");
            String age = rs.getString("age");
            String health = rs.getString("health");
            String parkId = rs.getString("parkid");

            oneRecord.addElement(animalId);
            oneRecord.addElement(name);
            oneRecord.addElement(kind);
            oneRecord.addElement(introduction);
            oneRecord.addElement(age);
            oneRecord.addElement(health);
            oneRecord.addElement(parkId);


            data.addElement(oneRecord);
        }
        return data;
    }

    //添加功能
    @Override
    public boolean add(AnimalEntity animalEntity) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO `animals`(`name`,`kind`,`introduction`,`age`,`health`,`parkid`)" +
                " VALUES (?,?,?,?,?,?)");

        Connection conn = null;
        PreparedStatement st = null;


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());

            st.setString(1, animalEntity.getName());
            st.setString(2, animalEntity.getKind());
            st.setString(3, animalEntity.getIntroduction());
            st.setString(4, animalEntity.getAge());
            st.setString(5, animalEntity.getHealth());
            st.setString(6, animalEntity.getParkId());

            return st.executeUpdate() == 1;


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            com.JdbcUtils_DBCP.release(conn, st, null);
        }
        return false;
    }

    //修改功能
    @Override
    public boolean update(AnimalEntity animalEntity) {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE `animals` SET `name` =?,`kind`=?,`introduction`=?, " +
                " `age`=?,`health`=?,`parkid`=? WHERE `id`=?");

        Connection conn = null;
        PreparedStatement st = null;


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());
            st.setString(1, animalEntity.getName());
            st.setString(2, animalEntity.getKind());
            st.setString(3, animalEntity.getIntroduction());
            st.setString(4, animalEntity.getAge());
            st.setString(5, animalEntity.getHealth());
            st.setString(6, animalEntity.getParkId());
            st.setInt(7, animalEntity.getAnimalId());


            return st.executeUpdate() == 1;


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            com.JdbcUtils_DBCP.release(conn, st, null);
        }
        return false;
    }

    @Override
    public boolean delete(AnimalEntity animalEntity) {
        StringBuilder sql = new StringBuilder();
        sql.append("delete from  `animals` WHERE `id`=?");

        Connection conn = null;
        PreparedStatement st = null;


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());

            st.setInt(1, animalEntity.getAnimalId());


            return st.executeUpdate() == 1;


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            com.JdbcUtils_DBCP.release(conn, st, null);
        }


        return false;
    }
}
